﻿using System;
using System.Collections.Generic;

using System.Text;
using System.Data;
namespace HZ.Supervise.DAL
{
    public class DAL_Prescription
    {
        /// <summary>
        /// 获取医院未发放的处方单
        /// </summary>
        /// <param name="stwhere"></param>
        /// <param name="currentPage"></param>
        /// <param name="pagesize"></param>
        /// <param name="totalRecord"></param>
        /// <returns></returns>
        public static DataTable GetHospitaleNoPrescrip(string stwhere, int currentPage, int pagesize, out int totalRecord)
        {
            totalRecord = 0;
            string strselect = "select max(PrescriptionId) as PrescriptionId,max(PAreaId) as PAreaId ,max(PHospitalId) as PHospitalId,max(PAreaname) as PAreaname,PHospitalName,sum(PtotalAmount) PtotalAmount,sum(PSubsidiesAmount) PSubsidiesAmount,sum(PSbsidyAmount) as PSbsidyAmount, min(Paddtime)as Paddtime,sum(PEssentialDrugsAmount) PEssentialDrugsAmount from dbo.TB_Prescription where 1=1 and PSbsidyAmount<>PSubsidiesAmount " + stwhere + "  group by PHospitalName order by PAreaname";
            HZ.Data.PageDescribe pdes = new HZ.Data.PageDescribe(strselect, pagesize);
            pdes.CurrentPage = currentPage;
            DataTable dt = pdes.GetSpecailPage();
            totalRecord = pdes.TotalCount;
            return dt;
        }
      
        /// <summary>
        /// 根据条件查询出可补贴的处方单
        /// </summary>
        /// <returns></returns>
        public static DataTable GetHospitaleTB_Prescription(string where)
        {
            string strselect = "select PSubsidiesAmount,PrescriptionId,PSbsidyAmount,PSubmitTime from TB_Prescription where PSbsidyAmount<>PSubsidiesAmount " + where + "order by PSubmitTime";
            DataTable dt = HZ.Data.DbHelperFactory.Create().ExecuteTable(strselect);
            return dt;
        }
        /// <summary>
        /// 获取医院未发放的处方单
        /// </summary>
        /// <param name="stwhere"></param>
        /// <param name="currentPage"></param>
        /// <param name="pagesize"></param>
        /// <param name="totalRecord"></param>
        /// <returns></returns>
        public static DataTable GetHospitaleNoPrescrip1(string stwhere)
        {
            string strselect = "select PrescriptionId,PAreaId ,PHospitalId,PAreaname,PHospitalName,sum(PtotalAmount) PtotalAmount,min(Paddtime)as Paddtime,sum(PEssentialDrugsAmount) PEssentialDrugsAmount from dbo.TB_Prescription where 1=1 " + stwhere + " group by PHospitalName, PAreaname,PAreaId,PHospitalId,PrescriptionId order by PAreaname";
            DataTable dt = HZ.Data.DbHelperFactory.Create().ExecuteTable(strselect);
            return dt;
        }
        /// <summary>
        /// 更新基要补贴
        /// </summary>
        /// <param name="SQL"></param>
        /// <returns></returns>
        public static int UpdtePEssentialDrugsAmount(string SQL)
        {
            int n = HZ.Data.DbHelperFactory.Create().ExecuteNonQuery(SQL);
            return n;
        }
        /// <summary>
        /// 获取补贴情况
        /// </summary>
        /// <param name="?"></param>
        /// <param name="?"></param>
        /// <param name="?"></param>
        /// <param name="?"></param>
        /// <returns></returns>
        public static DataTable GetSubsidy(string stwhere, int currentPage, int pagesize, out int totalRecord)
        {
            totalRecord = 0;
            string strselect = " select SubsidyID, Sareaid, Sareaname, SHospitalID,SsumAmount, SHospitalName, SubsidyAmount, Saddtime, case when SubsidyStartTime='1900-01-01 00:00:00.000' then  null else SubsidyStartTime end SubsidyStartTime, SubsidyEndTime, Remark,case State when 1 then '已发放未确认' when 2 then '已发放已确认' else '未知' end State, SconfirmationTime    from tb_Subsidy  where 1=1 " + stwhere + " order by Sareaid";
            HZ.Data.PageDescribe pdes = new HZ.Data.PageDescribe(strselect, pagesize);
            pdes.CurrentPage = currentPage;
            DataTable dt = pdes.GetSpecailPage();
            totalRecord = pdes.TotalCount;
            return dt;
        }
        public static DataTable GetSubsidyInfo(string stwhere, int currentPage, int pagesize, out int totalRecord)
        {
            totalRecord = 0;
            string strselect = " select SHospitalName, max(Sareaname)as Sareaname, max(SsumAmount) as SsumAmount from TB_Subsidy where 1=1 " + stwhere + " group by SHospitalName order by SHospitalName desc";
            HZ.Data.PageDescribe pdes = new HZ.Data.PageDescribe(strselect, pagesize);
            pdes.CurrentPage = currentPage;
            DataTable dt = pdes.GetSpecailPage();
            totalRecord = pdes.TotalCount;
            return dt;
        }
        /// <summary>
        /// 根据处方单明细表查询处方单
        /// </summary>
        /// <param name="stwhere"></param>
        /// <param name="currentPage"></param>
        /// <param name="pagesize"></param>
        /// <param name="totalRecord"></param>
        /// <returns></returns>
        public static DataTable GetHospitalePrescripByDetail(string stwhere, int currentPage, int pagesize, out int totalRecord)
        {
            totalRecord = 0;
            string strselect = "select a.PrescriptionId as PrescriptionId,a.PAreaId as PAreaId,a.PHospitalId as PHospitalId,a.PAreaname as PAreaname,a.PHospitalName as PHospitalName, a.PtotalAmount asPtotalAmount ,a.PEssentialDrugsAmount as from dbo.TB_Prescription as a left join TB_PrescriptionDetail b on a.PrescriptionId=b.PrescriptionID where 1=1 " + stwhere + "   order by PAreaname";
            HZ.Data.PageDescribe pdes = new HZ.Data.PageDescribe(strselect, pagesize);
            pdes.CurrentPage = currentPage;
            DataTable dt = pdes.GetSpecailPage();
            totalRecord = pdes.TotalCount;
            return dt;
        }
        /// <summary>
        /// 获取补贴处方单
        /// </summary>
        /// <param name="stwhere"></param>
        /// <param name="currentPage"></param>
        /// <param name="pagesize"></param>
        /// <param name="totalRecord"></param>
        /// <returns></returns>
        public static DataTable GetHospitalePrescripdeail(string stwhere, int currentPage, int pagesize, out int totalRecord)
        {
            totalRecord = 0;
            string strselect = "select PrescriptionId,PAreaId,PHospitalId,PAreaname,PHospitalName, PtotalAmount ,PEssentialDrugsAmount from dbo.TB_Prescription where 1=1 " + stwhere + "   order by PAreaname";
            HZ.Data.PageDescribe pdes = new HZ.Data.PageDescribe(strselect, pagesize);
            pdes.CurrentPage = currentPage;
            DataTable dt = pdes.GetSpecailPage();
            totalRecord = pdes.TotalCount;
            return dt;
        }
        /// <summary>
        /// 获取处方单明细
        /// </summary>
        /// <param name="stwhere"></param>
        /// <param name="currentPage"></param>
        /// <param name="pagesize"></param>
        /// <param name="totalRecord"></param>
        /// <returns></returns>
        public static DataTable GetHospitaleTB_PrescriptionDetail(string stwhere, int currentPage, int pagesize, out int totalRecord)
        {
            totalRecord = 0;
            string strselect = "select * from dbo.TB_PrescriptionDetail where 1=1 " + stwhere + "   order by Paddtime";
            HZ.Data.PageDescribe pdes = new HZ.Data.PageDescribe(strselect, pagesize);
            pdes.CurrentPage = currentPage;
            DataTable dt = pdes.GetSpecailPage();
            totalRecord = pdes.TotalCount;
            return dt;
        }
        public static DataTable GetHospitalePrescrioDetail2(string stwhere, int currentPage, int pagesize, out int totalRecord)
        {
            totalRecord = 0;
            string strselect = "select distinct a.* from dbo.TB_Prescription as a left join TB_PrescriptionDetail b on a.PrescriptionId=b.PrescriptionID where 1=1 and  a.PSubsidiesAmount<>a.PSbsidyAmount " + stwhere + " order by PAreaname";
            HZ.Data.PageDescribe pdes = new HZ.Data.PageDescribe(strselect, pagesize);
            pdes.CurrentPage = currentPage;
            DataTable dt = pdes.GetSpecailPage();
            totalRecord = pdes.TotalCount;
            return dt;
        }

        //显示明细
        public static DataTable GetHospitalePrescrioDetail2(string stwhere)
        {
            string strselect = string.Format(@"select * from dbo.TB_Prescription where PrescriptionId='{0}' ", stwhere);
            DataTable dt = HZ.Data.DbHelperFactory.Create().ExecuteTable(strselect);
            return dt;
        }


        /// <summary>
        /// 查询明细
        /// </summary>
        /// <param name="stwhere"></param>
        /// <param name="currentPage"></param>
        /// <param name="pagesize"></param>
        /// <param name="totalRecord"></param>
        /// <returns></returns>
        public static DataTable GetHospitaleSubsidyDetail(string stwhere, int currentPage, int pagesize, out int totalRecord)
        {
            totalRecord = 0;
            string strselect = "select * from dbo.TB_SubsidyDetail where 1=1 " + stwhere + "   order by Paddtime";
            HZ.Data.PageDescribe pdes = new HZ.Data.PageDescribe(strselect, pagesize);
            pdes.CurrentPage = currentPage;
            DataTable dt = pdes.GetSpecailPage();
            totalRecord = pdes.TotalCount;
            return dt;
        }
        /// <summary>
        /// 根据条件查询结算单
        /// </summary>
        /// <param name="stwhere"></param>
        /// <param name="currentPage"></param>
        /// <param name="pagesize"></param>
        /// <param name="totalRecord"></param>
        /// <returns></returns>
        public static DataTable GetAll(string stwhere, int currentPage, int pagesize, out int totalRecord)
        {
            totalRecord = 0;
            string strselect = "select  max(SubsidyName) as SubsidyName,sum(PSbsidyAmount) as PSbsidyAmount, max(SHospitalName) as SHospitalName,max(SubsidyAmount) as SubsidyAmount,max(Amount) as Amount,max(SsumAmount) as SsumAmount, max(Remark) as Remark, TB_Subsidy.SubsidyID from TB_Subsidy inner join TB_SubsidyDetail on TB_Subsidy.SubsidyID=TB_SubsidyDetail.SubsidyID where " + stwhere + " group by TB_Subsidy.SubsidyID order by SubsidyName";
            HZ.Data.PageDescribe pdes = new HZ.Data.PageDescribe(strselect, pagesize);
            pdes.CurrentPage = currentPage;
            DataTable dt = pdes.GetSpecailPage();
            totalRecord = pdes.TotalCount;
            return dt;
        }
        /// <summary>
        /// 提交数据表
        /// </summary>
        /// <param name="stwhere"></param>
        /// <returns></returns>
        public static DataTable GetSubmitData(string stwhere)
        {
            string strselect = "SELECT SubmitDataControlID,SubmitDataControlName,SisUsing,SChangetime,SChangeuserID,Sareaname from TB_SubmitDataControl where 1=1 " + stwhere;
            DataTable dt = HZ.Data.DbHelperFactory.Create().ExecuteTable(strselect);
            return dt;
        }
        /// <summary>
        /// 更新提交数据状态
        /// </summary>
        /// <param name="stupdate1">更新为1的ID</param>
        /// <param name="strupdate0">更新为 0 的ID</param>
        /// <returns></returns>
        public static int UpdateSubmitDataControlID(string stupdate1, string strupdate0, string struserid)
        {
            string update = "";

            if (!stupdate1.Equals(""))
            {
                update = "update TB_ProcureCatalog set IsShowProvince=1 ,SChangetime=GETDATE(),SChangeuserID='" + struserid + "' where ProcureCatalogID in(" + stupdate1.Remove(0, 1) + ")";
            }
            if (!strupdate0.Equals(""))
            {
                update += "update TB_ProcureCatalog set IsShowProvince=0 ,SChangetime=GETDATE(),SChangeuserID='" + struserid + "' where ProcureCatalogID in(" + strupdate0.Remove(0, 1) + ")";
            }
            return HZ.Data.DbHelperFactory.Create().ExecuteNonQuery(update);
        }
        /// <summary>
        /// 补助报表
        /// </summary>
        /// <param name="sqlwhere"></param>
        /// <param name="sqlgroup"></param>
        /// <param name="tpye">0:医院已确认补助,1:已补助</param>
        /// <returns></returns>
        public static DataTable GetSubsidyStatistics(string sqlwhere)
        {
            //StringBuilder sql = new StringBuilder();
            //sql.Append("select MAX(Sareaname) as Sareaname ,MAX(sareaid) as sareaid ,MAX(sHospitalId) as sHospitalId,MAX(SHospitalName) as SHospitalName,");
            //if (type == 0)
            //{
            //    sql.Append("dbo.fun_GetYearMonth(SconfirmationTime) as Time ,0 as temp ,SUM(Samount) as Samount,SUM(SubsidyAmount) as SubsidyAmount ,MAX(Sadduserid) as Sadduserid  from TB_Subsidy where 1=1 " + sqlwhere + " group by " + sqlgroup);
            //}
            //else
            //{
            //    sql.Append("dbo.fun_GetYearMonth(SubsidyEndTime) as Time ,0 as tempSUM(Samount) as Samount,SUM(SubsidyAmount) as SubsidyAmount ,MAX(Sadduserid) as Sadduserid  from TB_Subsidy where 1=1 " + sqlwhere + " group by " + sqlgroup);
            //}


            return HZ.Data.DbHelperFactory.Create().ExecuteTable(sqlwhere);
        }
        /// <summary>
        /// 批量更新Subsidy 0 失败1成功
        /// </summary>
        /// <param name="SubsidyList"></param>
        /// <returns></returns>
        public static int UpdateSubsidyState(IList<HZ.Supervise.Model.Subsidy> SubsidyList)
        {
            int flag = 0;
            var idb = HZ.Data.DbHelperFactory.Create();
            try
            {
                idb.BeginTrans();
                foreach (HZ.Supervise.Model.Subsidy item in SubsidyList)
                {
                    idb.ExecuteNonQuery(HZ.Data.DAL_Helper.GetUpdateSql<HZ.Supervise.Model.Subsidy>(item));
                    if (item.State == 2)
                    {
                        idb.ExecuteNonQuery(string.Format("update TB_Prescription set POrderStatus=4 where PrescriptionId in(select PrescriptionId from TB_SubsidyDetail where  SubsidyID='{0}')", item.SubsidyID));
                    }
                    else if (item.State == 1)
                    {
                        idb.ExecuteNonQuery(string.Format("update TB_Prescription set POrderStatus=3 where PrescriptionId in(select PrescriptionId from TB_SubsidyDetail where  SubsidyID='{0}')", item.SubsidyID));
                    }
                    else if (item.State == 0)
                    {
                        idb.ExecuteNonQuery(string.Format("update TB_Prescription set POrderStatus=2 where PrescriptionId in(select PrescriptionId from TB_SubsidyDetail where  SubsidyID='{0}')", item.SubsidyID));
                    }

                }
                idb.CommitTrans();
                flag = 1;
            }
            catch (Exception ee)
            {
                idb.RollTrans();
                HZ.Common.log4netManager.LogDebug("DAL_Prescription", ee.Message, ee);
                flag = 0;
            }
            return flag;

        }
       
        /// <summary>
        /// 批量删除SubsidyDetail
        /// </summary>
        /// <param name="SubsidyList"></param>
        /// <returns></returns>
        public static int DeleteSubsidyDetailBacth(IList<HZ.Supervise.Model.SubsidyDetail> SubsidyDetailList)
        {
            int flag = 0;
            var idb = HZ.Data.DbHelperFactory.Create();
            try
            {
                idb.BeginTrans();
                foreach (HZ.Supervise.Model.SubsidyDetail item in SubsidyDetailList)
                {
                    //删除TB_SubsidyDetail数据
                    string DelSubsidyDetail = string.Format("delete TB_SubsidyDetail where SubsidyDetailId='{0}'", item.SubsidyDetailId);
                    //修改TB_Subsidy里的
                    string UpSubsidy =string.Format("update TB_Subsidy set SubsidyAmount=(select SUM(PSbsidyAmount) from TB_SubsidyDetail where SubsidyID =(select SubsidyID from TB_SubsidyDetail where SubsidyDetailId='{0}')),Amount=(select SUM(PSubsidiesAmount) from TB_SubsidyDetail where SubsidyID =(select SubsidyID from TB_SubsidyDetail where SubsidyDetailId='{0}')),SsumAmount=(select SUM(PtotalAmount) from TB_SubsidyDetail where SubsidyID =(select SubsidyID from TB_SubsidyDetail where SubsidyDetailId='{0}')) where SubsidyID=(select SubsidyID from TB_SubsidyDetail where SubsidyDetailId='{1}')", item.SubsidyDetailId, item.SubsidyDetailId);
                    //修改TB_Prescription数据
                    string UpPrescription = string.Format(@"update TB_Prescription set PSbsidyAmount=pUnSubsidiesAmount,POrderStatus=1 where PrescriptionId='{0}'", item.PrescriptionId);
                    idb.ExecuteNonQuery(DelSubsidyDetail);
                    idb.ExecuteNonQuery(UpSubsidy);
                    idb.ExecuteNonQuery(UpPrescription);
                }
                idb.CommitTrans();
                flag = 1;
            }
            catch (Exception ee)
            {
                idb.RollTrans();
                HZ.Common.log4netManager.LogDebug("DAL_Prescription", ee.Message, ee);
                flag = 0;
            }
            return flag;
        }
        /// <summary>
        /// 批量删除Subsidy
        /// </summary>
        /// <param name="SubsidyList"></param>
        /// <returns></returns>
        public static int DeleteSubsidyBacth(IList<HZ.Supervise.Model.Subsidy> SubsidyList)
        {
            int flag = 0;
            var idb = HZ.Data.DbHelperFactory.Create();
            try
            {
                idb.BeginTrans();
                foreach (HZ.Supervise.Model.Subsidy item in SubsidyList)
                {
                    //修改TB_Prescription 里PSbsidyAmount为上次补助金额
                    string UpPrescription = string.Format(@"update TB_Prescription set PSbsidyAmount=pUnSubsidiesAmount ,POrderStatus=1 where PrescriptionId in (select PrescriptionId from  TB_SubsidyDetail where SubsidyID='{0}')", item.SubsidyID);
                    //删除TB_SubsidyDetail里的内容
                    string DelSubsidyDetail = string.Format("delete TB_SubsidyDetail where SubsidyID='{0}'", item.SubsidyID);
                    //删除TB_Subsidy里的数据
                    string DelSubsidy = string.Format("delete TB_Subsidy where SubsidyID='{0}'", item.SubsidyID);
                    idb.ExecuteNonQuery(UpPrescription);
                    idb.ExecuteNonQuery(DelSubsidyDetail);
                    idb.ExecuteNonQuery(DelSubsidy);
                }
                idb.CommitTrans();
                flag = 1;
            }
            catch (Exception ee)
            {
                idb.RollTrans();
                HZ.Common.log4netManager.LogDebug("DAL_Prescription", ee.Message, ee);
                flag = 0;
            }
            return flag;
        }
        /// <summary>
        /// 批量更新SubsidyDetail
        /// </summary>
        /// <param name="SubsidyDetailList"></param>
        /// <returns></returns>
        public static int UpdateSubsidyDetailBacth(IList<HZ.Supervise.Model.SubsidyDetail> SubsidyDetailList)
        {
            int flag = 0;
            var idb = HZ.Data.DbHelperFactory.Create();
            try
            {
                idb.BeginTrans();
                foreach (HZ.Supervise.Model.SubsidyDetail item in SubsidyDetailList)
                {
                    //修改TB_Prescription的PSbsidyAmount
                    idb.ExecuteNonQuery(string.Format("update TB_Prescription set PSbsidyAmount='{0}' where PrescriptionId =(select PrescriptionId from TB_SubsidyDetail where SubsidyDetailId='{1}')",item.PSbsidyAmount,item.SubsidyDetailId));
                    //修改TB_SubsidyDetail的PSbsidyAmount
                    idb.ExecuteNonQuery(string.Format("update TB_SubsidyDetail set PSbsidyAmount='{0}' ,PRemark='{1}' where SubsidyDetailId='{2}'", item.PSbsidyAmount,item.PRemark, item.SubsidyDetailId));
                    string sql = (string.Format("update TB_Subsidy set SubsidyAmount=(select SUM(PSbsidyAmount) from TB_SubsidyDetail where SubsidyID =(select SubsidyID from TB_SubsidyDetail where SubsidyDetailId='{0}')),Amount=(select SUM(PSubsidiesAmount) from TB_SubsidyDetail where SubsidyID =(select SubsidyID from TB_SubsidyDetail where SubsidyDetailId='{0}')),SsumAmount=(select SUM(PtotalAmount) from TB_SubsidyDetail where SubsidyID =(select SubsidyID from TB_SubsidyDetail where SubsidyDetailId='{0}')) where SubsidyID=(select SubsidyID from TB_SubsidyDetail where SubsidyDetailId='{1}')", item.SubsidyDetailId, item.SubsidyDetailId));
                    //修改TB_Subsidy的SubsidyAmount
                    idb.ExecuteNonQuery(string.Format("update TB_Subsidy set SubsidyAmount=(select SUM(PSbsidyAmount) from TB_SubsidyDetail where SubsidyID =(select SubsidyID from TB_SubsidyDetail where SubsidyDetailId='{0}')),Amount=(select SUM(PSubsidiesAmount) from TB_SubsidyDetail where SubsidyID =(select SubsidyID from TB_SubsidyDetail where SubsidyDetailId='{0}')),SsumAmount=(select SUM(PtotalAmount) from TB_SubsidyDetail where SubsidyID =(select SubsidyID from TB_SubsidyDetail where SubsidyDetailId='{0}')) where SubsidyID=(select SubsidyID from TB_SubsidyDetail where SubsidyDetailId='{1}')", item.SubsidyDetailId, item.SubsidyDetailId));
                   
                }
                idb.CommitTrans();
                flag = 1;
            }
            catch (Exception ee)
            {
                idb.RollTrans();
                HZ.Common.log4netManager.LogDebug("DAL_Prescription", ee.Message, ee);
                flag = 0;
            }
            return flag;
        }
        /// <summary>
        /// 向Subsidy表里添加SubsidyDetail
        /// </summary>
        /// <param name="SubsidyDetailList"></param>
        /// <returns></returns>
        public static int AddSubsidyDetailToSubsidy(IList<HZ.Supervise.Model.SubsidyDetail> SubsidyDetailList)
        {
            int flag = 0;
            var idb = HZ.Data.DbHelperFactory.Create();
            try
            {
                idb.BeginTrans();
                foreach (HZ.Supervise.Model.SubsidyDetail item in SubsidyDetailList)
                {
//                    idb.ExecuteNonQuery(string.Format("update TB_Prescription set POrderStatus=2,PSbsidyAmount={0}  where PrescriptionId='{1}'", item.PSbsidyAmount,item.PrescriptionId));
//                    //string sq = string.Format("update TB_Prescription set POrderStatus=2,PSbsidyAmount={0} ,pUnSubsidiesAmount=(select PSbsidyAmount from TB_Prescription where PrescriptionId='"+item.PrescriptionId+"') where PrescriptionId='{1}'", item.PSbsidyAmount, item.PrescriptionId);
                 
//                    string sql2=(string.Format(@"insert into TB_SubsidyDetail
//                                    (SubsidyID, SubsidyDetailId, PAreaId, PAreaname, PHospitalId, PHospitalName, PatientName, PHisPId, PdoctorName, PtotalAmount, PEssentialDrugsAmount, PSbsidyAmount, PAge, PSex, Paddtime, PisSubsidy, PSubsidyTime, PSubsidyUserid, PsympToms, PrescriptionName, PSubsidiesAmount, PRemark, POrderStatus,PrescriptionId)
//                                    select '{0}' SubsidyID,'{1}' SubsidyDetailId, PAreaId, PAreaname, PHospitalId, PHospitalName, PatientName, PHisPId, PdoctorName, PtotalAmount, PEssentialDrugsAmount, {2}, PAge, PSex,Paddtime, PisSubsidy,  PSubsidyTime, PSubsidyUserid, PsympToms,PrescriptionName,PSubsidiesAmount,PRemark,2,PrescriptionId
//                                    from TB_Prescription where PrescriptionId='{3}'",item.SubsidyID,item.SubsidyDetailId,item.PSbsidyAmount,item.PrescriptionId));
//                    idb.ExecuteNonQuery(sql2);
//                    //string sql = string.Format(@"update TB_Subsidy set SubsidyAmount=(select SUM(PSbsidyAmount) from TB_SubsidyDetail where SubsidyID ='{0}') where SubsidyID='{1}'", item.SubsidyID, item.SubsidyID);
                    idb.ExecuteNonQuery(string.Format(@"update TB_Subsidy set SubsidyAmount=(select SUM(PSbsidyAmount) from TB_SubsidyDetail where SubsidyID ='{0}'),Amount=(select SUM(PSubsidiesAmount) from TB_SubsidyDetail where SubsidyID ='{1}'),SsumAmount=(select SUM(PtotalAmount) from TB_SubsidyDetail where SubsidyID ='{2}') where SubsidyID='{3}'", item.SubsidyID, item.SubsidyID, item.SubsidyID, item.SubsidyID));
                }
                idb.CommitTrans();
                flag = 1;
            }
            catch (Exception ee)
            {
                idb.RollTrans();
                HZ.Common.log4netManager.LogDebug("DAL_Prescription", ee.Message, ee);
                flag = 0;
            }
            return flag;
        }
        /// <summary>
        /// 更新或插入结算单或处方单明细
        /// </summary>
        /// <param name="subsidy">结算单</param>
        /// <param name="prescriotionList">处方单集合</param>
        /// <param name="i">参数若为0时添加结算单，若为其他时更新结算单</param>
        /// <returns></returns>
        public static int UpdateAll(Model.Subsidy subsidy,IList<Model.Prescription> prescriotionList, int i) 
        {
            int flag = 0;
            var idb = HZ.Data.DbHelperFactory.Create();
            try
            {
                idb.BeginTrans();
                if (i == 0)
                {
                   int k= DAL_Helper.InsertModel(subsidy);
                    foreach (var item in prescriotionList)
                    {
                        DAL_Helper.UpdateModel(item);
                        idb.ExecuteNonQuery("insert dbo.TB_SubsidyDetail([SubsidyDetailId],[SubsidyID] ,[PAreaId],[PAreaname],[PHospitalId],[PHospitalName],[PatientName],[PHisPId] ,[PdoctorName],[PtotalAmount],[PEssentialDrugsAmount],[PSbsidyAmount],[PAge],[PSex],[Paddtime] ,[PisSubsidy],[PSubsidyTime],[PSubsidyUserid],[PsympToms] ,[POrderStatus] ,[PRemark],[PrescriptionName],PrescriptionId,PSubsidiesAmount) select '" + Guid.NewGuid().ToString() + "','" + subsidy.SubsidyID + "',[PAreaId],[PAreaname],[PHospitalId],[PHospitalName],[PatientName],[PHisPId] ,[PdoctorName],[PtotalAmount],[PEssentialDrugsAmount]," + item.PSbsidyAmount + ",[PAge],[PSex],[Paddtime] ,[PisSubsidy],[PSubsidyTime],[PSubsidyUserid],[PsympToms] ,[POrderStatus] ,[PRemark],[PrescriptionName],'" + item.PrescriptionId+ "',PSubsidiesAmount  from TB_Prescription where 1=1 and PrescriptionID ='" + item.PrescriptionId + "'");
                    }
                }
                else 
                {
                 
                    foreach (var item in prescriotionList)
                    {
                        DAL_Helper.UpdateModel(item);
                        idb.ExecuteNonQuery("insert dbo.TB_SubsidyDetail([SubsidyDetailId],[SubsidyID] ,[PAreaId],[PAreaname],[PHospitalId],[PHospitalName],[PatientName],[PHisPId] ,[PdoctorName],[PtotalAmount],[PEssentialDrugsAmount],[PSbsidyAmount],[PAge],[PSex],[Paddtime] ,[PisSubsidy],[PSubsidyTime],[PSubsidyUserid],[PsympToms] ,[POrderStatus] ,[PRemark],[PrescriptionName],PrescriptionId,PSubsidiesAmount) select '" + Guid.NewGuid().ToString() + "','" + subsidy.SubsidyID + "',[PAreaId],[PAreaname],[PHospitalId],[PHospitalName],[PatientName],[PHisPId] ,[PdoctorName],[PtotalAmount],[PEssentialDrugsAmount]," + item.PSbsidyAmount + ",[PAge],[PSex],[Paddtime] ,[PisSubsidy],[PSubsidyTime],[PSubsidyUserid],[PsympToms] ,[POrderStatus] ,[PRemark],[PrescriptionName],'" + item.PrescriptionId + "',PSubsidiesAmount  from TB_Prescription where 1=1 and PrescriptionID ='" + item.PrescriptionId + "'");
                    }
                    idb.ExecuteNonQuery(string.Format(@"update TB_Subsidy set SubsidyAmount=(select SUM(PSbsidyAmount) from TB_SubsidyDetail where SubsidyID ='{0}'),Amount=(select SUM(PSubsidiesAmount) from TB_SubsidyDetail where SubsidyID ='{1}'),SsumAmount=(select SUM(PtotalAmount) from TB_SubsidyDetail where SubsidyID ='{2}') where SubsidyID='{3}'", subsidy.SubsidyID, subsidy.SubsidyID, subsidy.SubsidyID, subsidy.SubsidyID));
                }
                idb.CommitTrans();
                flag = 1;
            }
            catch (Exception ee)
            {
                idb.RollTrans();
                HZ.Common.log4netManager.LogDebug("DAL_Prescription", ee.Message, ee);
                flag = 0;
            }
            return flag;
         
        }
    }
}
